package com.example.demo.repository;


import com.example.demo.model.StudentAssignment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface StudentAssignmentRepository extends JpaRepository<StudentAssignment, Long> {
    Optional<StudentAssignment> findByAssignmentIdAndStudentId(Long assignmentId, Long studentId);

    List<StudentAssignment> findByAssignmentId(Long assignmentId);

    List<StudentAssignment> findByStudentId(Long studentId);

    @Query("SELECT sa FROM StudentAssignment sa WHERE sa.assignment.course.id = :courseId AND sa.student.id = :studentId")
    List<StudentAssignment> findByCourseIdAndStudentId(Long courseId, Long studentId);

    @Query("SELECT COUNT(sa) FROM StudentAssignment sa WHERE sa.assignment.id = :assignmentId AND sa.status = 'SUBMITTED'")
    long countSubmittedStudents(Long assignmentId);
    @Query("SELECT COUNT(sa) FROM StudentAssignment sa WHERE sa.assignment.id = ?1 AND sa.status = 'SUBMITTED'")
    long countSubmittedStudents(Long assignmentId);

    @Query("SELECT COUNT(sa) FROM StudentAssignment sa WHERE sa.assignment.id = ?1 AND sa.status = 'GRADED'")
    long countGradedStudents(Long assignmentId);

    @Query("SELECT AVG(sa.grade) FROM StudentAssignment sa WHERE sa.assignment.id = ?1 AND sa.grade IS NOT NULL")
    Double calculateAverageGrade(Long assignmentId);

    @Query("SELECT MAX(sa.grade) FROM StudentAssignment sa WHERE sa.assignment.id = ?1 AND sa.grade IS NOT NULL")
    Integer findMaxGrade(Long assignmentId);

    @Query("SELECT MIN(sa.grade) FROM StudentAssignment sa WHERE sa.assignment.id = ?1 AND sa.grade IS NOT NULL")
    Integer findMinGrade(Long assignmentId);
}